package nus.edu.sg.db;

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;

import oracle.jdbc.internal.OracleTypes;
import nus.edu.sg.object.ParameterConstant;

public class DBDashboardCmd {
	
	
	public boolean startSchdular(int x, int y, int z) throws SQLException {
		
        Connection con = DBConnection.getInstance().getConnection();	
		
        CallableStatement cs = null;
		
		try {
			cs = con.prepareCall("{call PKG_DB_MON.PROC_WR_SCHED_INITIALIZE(?,?,?)}");
			
	        cs.setInt(1, x);
	        cs.setInt(2, y);
	        cs.setInt(3, z);
	        cs.execute();
	        
	
		} catch (SQLException e ) {		
		
		System.out.println(e.getMessage());
		throw e;
		
		}
		finally
		{
			con.close();
		}
				
		return true;
	}
	
	public BigDecimal getStatOverall(String parameterId,
			Timestamp starTime, Timestamp endTime) throws SQLException {
		
		
       Connection con = DBConnection.getInstance().getConnection();	
	   CallableStatement cs = null;
       BigDecimal overallVal = null;
	
		try {
			
			ParameterConstant.ParamType pramType = Enum.valueOf(ParameterConstant.ParamType.class, parameterId);
			
			
			cs = con.prepareCall("{ call PKG_DB_MON.PROC_GET_STAT_OVERALL(?,?,?,?)}");
			
	        cs.setInt(1, pramType.id);
	        cs.setTimestamp(2, starTime);
	        cs.setTimestamp(3, endTime);
	        cs.registerOutParameter(4, java.sql.Types.DOUBLE);
	        
	        cs.execute();
	        
	        overallVal = new BigDecimal(cs.getDouble(4));
	        System.out.println("Overall perormance value : " +overallVal);
	        
	
		} catch (SQLException e ) {		
		
		System.out.println(e.getMessage());
		throw e;
		
		}
		finally 
		{
			con.close();
		}
				
		return overallVal;
	}

	public HashMap<Long, String> getStatLevel(String parameterId,
			Timestamp starTime, Timestamp endTime, String level) throws SQLException {
		
	   
       Connection con = DBConnection.getInstance().getConnection();	
	   CallableStatement cs = null;
       HashMap<Long, String> statMap = new HashMap<Long, String>() ;
	
		try {
			
			ParameterConstant.ParamType pramType = Enum.valueOf(ParameterConstant.ParamType.class, parameterId);
			ParameterConstant.BreakDownLevel breakDownLlevel = Enum.valueOf(ParameterConstant.BreakDownLevel.class, level);
			cs = con.prepareCall("{ call PKG_DB_MON.PROC_GET_STAT_LEVEL(?,?,?,?,?)}");
			
	        cs.setInt(1, pramType.id);
	        cs.setTimestamp(2, starTime);
	        cs.setTimestamp(3, endTime);
	        cs.setInt(4, breakDownLlevel.id);
	        cs.registerOutParameter(5, OracleTypes.CURSOR);
	        
	        cs.execute();
	        
	        ResultSet rset = (ResultSet)cs.getObject(5);
	        while (rset.next ()) 
	        {
		          System.out.println( rset.getString (1)+" value "+ rset.getDouble(2)); 
		          statMap.put(rset.getDate(1).getTime(), Double.toString(rset.getDouble(2)));
		          
	        }
	        
	
		} catch (SQLException e ) {		
		
		System.out.println(e.getMessage());
		throw e;
		
		}
		finally 
		{
			con.close();
		}
				
		return statMap;
	}
	
	
	public ArrayList <String[]> getSqlQueryResult(String query) throws SQLException
	{
		   Connection con = null;	
		   ResultSet rset = null;
		   PreparedStatement ps = null;		   
	       String[] columnArray=null;
	       ArrayList <String[]> result = new ArrayList<String[]>();
		
			try {
				con = DBConnection.getInstance().getConnection();
				query = query.trim();
				int spaceIdx = query.indexOf(" ");
				String type = query.substring(0,spaceIdx);
				
				if(type.equalsIgnoreCase("SELECT")){				 
				
				ps = con.prepareStatement(query);				     
		        ps.execute();		        
		        rset = ps.getResultSet();		        
		        ResultSetMetaData rsmd = rset.getMetaData();
		        int columnCount=rsmd.getColumnCount();
		        columnArray=new String[columnCount];
		        for(int i=1;i<=columnCount;i++)
		        {
		        	columnArray[i-1]=rsmd.getColumnName(i); 
		        }
		        result.add(columnArray);
		        while (rset.next ()) 
		        {
		        	String[] row = new String[columnCount];
		            for (int i=0; i <columnCount ; i++)
		            {
		               row[i] = rset.getString(i + 1);
		            }
		            result.add(row);			          
		        }
		        
				}else{
					
					ps = con.prepareStatement(query);
					ps.executeUpdate();					
					columnArray = new String[1];
					String message = "";
					if(type.equalsIgnoreCase("INSERT")){
						message = "Insert successful";
					}else if(type.equalsIgnoreCase("UPDATE")){
						message = "Update successful";
					}else if(type.equalsIgnoreCase("DELETE")){
						message = "Delete successful";
					}else if(type.equalsIgnoreCase("createtable")){
						message = "Table create successful";
					}else if(type.equalsIgnoreCase("altertable")){
						message = "Table altered successful";
					}else{
						message = "Statement execute successful";
					}
					
					columnArray[0] = message;
					result.add(columnArray);
					
		        
				}
		
			} catch (SQLException e ) {		
			columnArray = new String[1];
			columnArray[0] = e.getMessage();
			result.add(columnArray);
			System.out.println(e.getMessage());
			//throw e;
			
			}
			finally 
			{
				if(rset != null){
					rset.close();
				}
				if(ps != null){
					ps.close();
				}
				if(con != null){
					con.close();
				}
				
				
			}
			return result;
			
	}

}
